<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTaobaoOrder extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $sql = <<<SQL
        create table taoke_taobao_order
        (
            id int(10) unsigned not null auto_increment
                primary key,
            click_time timestamp null default null comment '点击时间',
            tbid varchar(100) not null comment '宝贝ID',
            order_id varchar(128) not null comment '订单编号',
            site_id int(10) unsigned not null comment '来源媒体ID',
            adzone_id int(10) unsigned not null comment '广告位ID',
            item_num int(10) unsigned not null comment '商品数',
            title varchar(120) default '0000-00-00 00:00:00' not null comment '淘宝标题',
            shop_name varchar(100) not null comment '所属店铺名称',
            item_price decimal(13,2) unsigned not null comment '商品价格',
            status tinyint(3) unsigned not null comment '订单状态',
            source tinyint(3) unsigned not null comment '订单来源（淘宝，天猫，聚划算）',
            profit_rate decimal(5,4) unsigned not null comment '收入比率',
            share_rate decimal(5,4) unsigned not null comment '分成比率',
            expected_profit decimal(13,2) unsigned not null comment '效果预估',
            settled_amount decimal(13,2) unsigned not null comment '结算金额',
            expected_share decimal(13,2) unsigned not null comment '预估收入',
            commission_rate decimal(5,4) unsigned not null comment '佣金比率',
            commission_amount decimal(13,2) unsigned not null comment '佣金金额',
            subsidy_rate decimal(5,4) unsigned not null comment '补贴比率',
            subsidy_amount decimal(13,2) unsigned not null comment '补贴金额',
            subsidy_kind varchar(20) null comment '补贴类型',
            user_agent tinyint(3) unsigned not null comment '成交平台（PC/无线）',
            third_party_service_source varchar(20) null comment '第三方服务来源',
            category_name varchar(200) not null comment '类目名称',
            settled_time timestamp null comment '结算时间',
            account_id int(10) unsigned not null comment 'pid_account.id',
            paid_amount decimal(13,2) unsigned not null comment '付款金额',
            create_time timestamp default CURRENT_TIMESTAMP not null comment '订单创建时间',
            update_time timestamp default CURRENT_TIMESTAMP not null
        )
        comment '阿里妈妈订单详情表'
        ;
SQL;
        if (!DB::statement($sql)) {
            throw new \Exception("failed on execute SQL: [{$sql}]");
        }

        $sql = <<<SQL
        
        create index idx_adzone
            on taoke_taobao_order (account_id, site_id, adzone_id)
        ;

SQL;
        if (!DB::statement($sql)) {
            throw new \Exception("failed on execute SQL: [{$sql}]");
        }

        $sql = <<<SQL
        
        create index idx_tbid
            on taoke_taobao_order (tbid)
        ;

SQL;
        if (!DB::statement($sql)) {
            throw new \Exception("failed on execute SQL: [{$sql}]");
        }

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        throw new \Exception('downgrade is forbidden');
    }
}
